import pandas as pd
import os
import re

# 文件夹路径
input_folder = r'c:\Users\admin\Desktop\homework\课程信息'
output_folder = r'c:\Users\admin\Desktop\homework\13周'

# 确保输出文件夹存在
os.makedirs(output_folder, exist_ok=True)

# 存储所有课程和教师信息
all_courses = set()
teacher_courses = {}

# 遍历所有Excel文件
for filename in os.listdir(input_folder):
    if filename.endswith('.xls'):
        file_path = os.path.join(input_folder, filename)
        print(f'处理文件: {filename}')
        
        # 读取Excel文件
        df = pd.read_excel(file_path)
        
        # 遍历每个单元格，提取课程信息
        for index, row in df.iterrows():
            for col in df.columns:
                cell_value = row[col]
                if pd.notna(cell_value):
                    # 将单元格值转换为字符串
                    cell_str = str(cell_value)
                    
                    # 先按换行符分割单元格内容，处理同一单元格中的多个课程
                    cell_lines = cell_str.split('\r\n')
                    for line in cell_lines:
                        line = line.strip()
                        if not line:
                            continue
                        
                        # 查找课程信息，格式为：课程名称/(节次)周次/ 地点/教师/人数
                        course_pattern = r'([^/]+)/(?:\([^)]+\))?([^/]+)/\s*([^/]+)/([^/]+)/\d+'
                        match = re.match(course_pattern, line)
                        
                        if match:
                            course_name = match.group(1).strip()
                            teacher_name = match.group(4).strip()
                            
                            # 添加到课程集合
                            all_courses.add(course_name)
                            
                            # 添加到教师授课字典
                            if teacher_name not in teacher_courses:
                                teacher_courses[teacher_name] = set()
                            teacher_courses[teacher_name].add(course_name)
                        else:
                            # 尝试匹配其他格式的课程信息，如：实践课程：美育★李红(共1周)/1周;
                            other_course_pattern = r'(?:实践课程|其他课程)：([^★☆]+)[★☆]([^(]+)' 
                            match = re.match(other_course_pattern, line)
                            if match:
                                course_name = match.group(1).strip()
                                teacher_name = match.group(2).strip().rstrip('(')
                                
                                # 添加到课程集合
                                all_courses.add(course_name)
                                
                                # 添加到教师授课字典
                                if teacher_name not in teacher_courses:
                                    teacher_courses[teacher_name] = set()
                                teacher_courses[teacher_name].add(course_name)
                            else:
                                # 尝试匹配特殊格式，如：体育与健康（二）(板块)
                                special_course_pattern = r'([^()]+(?:（[^()]+）)?)\s*\((?:板块|专用)\)'
                                match = re.match(special_course_pattern, line)
                                if match:
                                    course_name = match.group(1).strip()
                                    # 由于没有教师信息，我们只添加课程名称
                                    all_courses.add(course_name)
                                # 检查是否包含已知的课程关键字
                                known_course_keywords = ['体育与健康（二）', '创新创业']
                                for keyword in known_course_keywords:
                                    if keyword in line:
                                        all_courses.add(keyword)

# 整理课程列表（排序）
sorted_courses = sorted(list(all_courses))

# 整理教师授课列表（排序）
sorted_teacher_courses = {}
for teacher in sorted(teacher_courses.keys()):
    sorted_teacher_courses[teacher] = sorted(list(teacher_courses[teacher]))

# 保存课程列表到文件
courses_file = os.path.join(output_folder, '课程列表.txt')
with open(courses_file, 'w', encoding='utf-8') as f:
    f.write('课程列表：\n')
    f.write('=' * 50 + '\n')
    for i, course in enumerate(sorted_courses, 1):
        f.write(f'{i}. {course}\n')

# 保存教师授课列表到文件
teachers_file = os.path.join(output_folder, '教师授课列表.txt')
with open(teachers_file, 'w', encoding='utf-8') as f:
    f.write('教师授课列表：\n')
    f.write('=' * 50 + '\n')
    for teacher, courses in sorted_teacher_courses.items():
        f.write(f'\n{teacher}：\n')
        for course in courses:
            f.write(f'  - {course}\n')

print('\n处理完成！')
print(f'课程列表已保存到：{courses_file}')
print(f'教师授课列表已保存到：{teachers_file}')